--    Program: 		CAPPOEXT_SUPPLIER_SITE.sql
--    Description:	Extract PO Supplier Site information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(100);
   
   l_set_of_books_id		number;
   
   cursor cur_system_option (p_set_of_books_id number) is
      select pvs.* 
      from   PO_VENDOR_SITES_ALL pvs, HR_ORGANIZATION_INFORMATION hoi
      where	 pvs.org_id = hoi.organization_id 
      and    hoi.org_information_context = 'Operating Unit Information' 
      and    hoi.org_information3 = to_char(p_set_of_books_id);

begin
   l_pathname := '/tmp';
   l_filename := 'CAPPO_SUPPLIER_SITE.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
   
   begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
   end;      

   for cur in cur_system_option(l_set_of_books_id) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file, 
	   cur.vendor_site_id|| chr(9) ||
	   cur.LAST_UPDATE_DATE|| chr(9) ||
	   cur.last_updated_by|| chr(9) ||
	   cur.vendor_id|| chr(9) ||
	   cur.vendor_site_code|| chr(9) ||
	   cur.vendor_site_code_alt|| chr(9) ||
	   cur.last_update_login|| chr(9) ||
	   cur.creation_date|| chr(9) ||
	   cur.created_by|| chr(9) ||
       cur.PURCHASING_SITE_FLAG           || chr(9) ||
       cur.RFQ_ONLY_SITE_FLAG             || chr(9) ||
       cur.PAY_SITE_FLAG                  || chr(9) ||
       cur.ATTENTION_AR_FLAG              || chr(9) ||
       cur.ADDRESS_LINE1                  || chr(9) ||
       replace(cur.address_lines_alt, chr(10), '||')  || chr(9) ||
       cur.ADDRESS_LINE2                  || chr(9) ||
       cur.ADDRESS_LINE3                  || chr(9) ||
       cur.CITY                           || chr(9) ||
       cur.STATE                          || chr(9) ||
       cur.ZIP                            || chr(9) ||
       cur.PROVINCE                       || chr(9) ||
       cur.COUNTRY                        || chr(9) ||
       cur.AREA_CODE                      || chr(9) ||
       cur.PHONE                          || chr(9) ||
       cur.CUSTOMER_NUM                   || chr(9) ||
       cur.SHIP_TO_LOCATION_ID            || chr(9) ||
       cur.BILL_TO_LOCATION_ID            || chr(9) ||
       cur.SHIP_VIA_LOOKUP_CODE           || chr(9) ||
       cur.FREIGHT_TERMS_LOOKUP_CODE      || chr(9) ||
       cur.FOB_LOOKUP_CODE                || chr(9) ||
       cur.INACTIVE_DATE                  || chr(9) ||
       cur.FAX                            || chr(9) ||
       cur.FAX_AREA_CODE                  || chr(9) ||
       cur.TELEX                          || chr(9) ||
       cur.PAYMENT_METHOD_LOOKUP_CODE     || chr(9) ||
       cur.BANK_ACCOUNT_NAME              || chr(9) ||
       cur.BANK_ACCOUNT_NUM               || chr(9) ||
       cur.BANK_NUM                       || chr(9) ||
       cur.BANK_ACCOUNT_TYPE              || chr(9) ||
       cur.TERMS_DATE_BASIS               || chr(9) ||
       cur.CURRENT_CATALOG_NUM            || chr(9) ||
       cur.VAT_CODE                       || chr(9) ||
       cur.DISTRIBUTION_SET_ID            || chr(9) ||
       cur.ACCTS_PAY_CODE_COMBINATION_ID  || chr(9) ||
       cur.PREPAY_CODE_COMBINATION_ID     || chr(9) ||
       cur.PAY_GROUP_LOOKUP_CODE          || chr(9) ||
       cur.PAYMENT_PRIORITY               || chr(9) ||
       cur.TERMS_ID                       || chr(9) ||
       cur.INVOICE_AMOUNT_LIMIT           || chr(9) ||
       cur.PAY_DATE_BASIS_LOOKUP_CODE     || chr(9) ||
       cur.ALWAYS_TAKE_DISC_FLAG          || chr(9) ||
       cur.INVOICE_CURRENCY_CODE          || chr(9) ||
       cur.PAYMENT_CURRENCY_CODE          || chr(9) ||
       cur.HOLD_ALL_PAYMENTS_FLAG         || chr(9) ||
       cur.HOLD_FUTURE_PAYMENTS_FLAG      || chr(9) ||
       cur.HOLD_REASON                    || chr(9) ||
       cur.HOLD_UNMATCHED_INVOICES_FLAG   || chr(9) ||
       cur.AP_TAX_ROUNDING_RULE           || chr(9) ||
       cur.AUTO_TAX_CALC_FLAG             || chr(9) ||
       cur.AUTO_TAX_CALC_OVERRIDE         || chr(9) ||
       cur.AMOUNT_INCLUDES_TAX_FLAG       || chr(9) ||
       cur.EXCLUSIVE_PAYMENT_FLAG         || chr(9) ||
       cur.TAX_REPORTING_SITE_FLAG        || chr(9) ||
       cur.ATTRIBUTE_CATEGORY             || chr(9) ||
       cur.ATTRIBUTE1                     || chr(9) ||
       cur.ATTRIBUTE2                     || chr(9) ||
       cur.ATTRIBUTE3                     || chr(9) ||
       cur.ATTRIBUTE4                     || chr(9) ||
       cur.ATTRIBUTE5                     || chr(9) ||
       cur.ATTRIBUTE6                     || chr(9) ||
       cur.ATTRIBUTE7                     || chr(9) ||
       cur.ATTRIBUTE8                     || chr(9) ||
       cur.ATTRIBUTE9                     || chr(9) ||
       cur.ATTRIBUTE10                    || chr(9) ||
       cur.ATTRIBUTE11                    || chr(9) ||
       cur.ATTRIBUTE12                    || chr(9) ||
       cur.ATTRIBUTE13                    || chr(9) ||
       cur.ATTRIBUTE14                    || chr(9) ||
       cur.ATTRIBUTE15                    || chr(9) ||
       cur.REQUEST_ID                     || chr(9) ||
       cur.PROGRAM_APPLICATION_ID         || chr(9) ||
       cur.PROGRAM_ID                     || chr(9) ||
       cur.PROGRAM_UPDATE_DATE            || chr(9) ||
       cur.VALIDATION_NUMBER              || chr(9) ||
       cur.EXCLUDE_FREIGHT_FROM_DISCOUNT  || chr(9) ||
       cur.VAT_REGISTRATION_NUM           || chr(9) ||
       cur.OFFSET_VAT_CODE                || chr(9) ||
       cur.ORG_ID                         || chr(9) ||
       cur.CHECK_DIGITS                   || chr(9) ||
       cur.BANK_NUMBER                    || chr(9) ||
       cur.ADDRESS_LINE4                  || chr(9) ||
       cur.COUNTY                         || chr(9) ||
       cur.ADDRESS_STYLE                  || chr(9) ||
       cur.LANGUAGE                       || chr(9) ||
       cur.ALLOW_AWT_FLAG                 || chr(9) ||
       cur.AWT_GROUP_ID                   || chr(9) ||
       cur.GLOBAL_ATTRIBUTE1              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE2              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE3              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE4              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE5              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE6              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE7              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE8              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE9              || chr(9) ||
       cur.GLOBAL_ATTRIBUTE10             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE11             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE12             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE13             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE14             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE15             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE16             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE17             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE18             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE19             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE20             || chr(9) ||
       cur.GLOBAL_ATTRIBUTE_CATEGORY      || chr(9) ||
       cur.EDI_TRANSACTION_HANDLING       || chr(9) ||
       cur.EDI_ID_NUMBER                  || chr(9) ||
       cur.EDI_PAYMENT_METHOD             || chr(9) ||
       cur.EDI_PAYMENT_FORMAT             || chr(9) ||
       cur.EDI_REMITTANCE_METHOD          || chr(9) ||
       cur.BANK_CHARGE_BEARER             || chr(9) ||
       cur.EDI_REMITTANCE_INSTRUCTION     || chr(9) ||
       cur.BANK_BRANCH_TYPE               || chr(9) ||
       cur.PAY_ON_CODE                    || chr(9) ||
       cur.DEFAULT_PAY_SITE_ID            || chr(9) ||
       cur.PAY_ON_RECEIPT_SUMMARY_CODE    || chr(9) ||
       cur.TP_HEADER_ID                   || chr(9) ||
       cur.ECE_TP_LOCATION_CODE           || chr(9) ||
       cur.PCARD_SITE_FLAG                || chr(9) ||
       cur.MATCH_OPTION                   || chr(9) ||
       cur.COUNTRY_OF_ORIGIN_CODE         || chr(9) ||
       cur.FUTURE_DATED_PAYMENT_CCID      || chr(9) ||
       cur.CREATE_DEBIT_MEMO_FLAG         || chr(9) ||
       cur.OFFSET_TAX_FLAG                || chr(9) ||
       cur.SUPPLIER_NOTIF_METHOD          || chr(9) ||
       cur.EMAIL_ADDRESS                  || chr(9) ||
       cur.REMITTANCE_EMAIL               || chr(9) ||
       cur.PRIMARY_PAY_SITE_FLAG
	 );
     
	 l_processed_count := l_processed_count + 1;
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Supplier Site not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPPOEXT_SUPPLIER_SITE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
